"""
Copyright (c) 2022 Huawei Technologies Co.,Ltd.

openGauss is licensed under Mulan PSL v2.
You can use this software according to the terms and conditions of the Mulan PSL v2.
You may obtain a copy of Mulan PSL v2 at:

          http://license.coscl.org.cn/MulanPSL2

THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
See the Mulan PSL v2 for more details.
"""
"""
Case Type   : dolphin
Case Name   : 验证兼容mysql侧时间日期型函数
Create At   : 2022.04.06
Owner       : z@wan005
Description :
    1、创建b库
    2、b库下查询dolphin插件
    3、验证dayofmonth函数,返回日期
    4、验证dayofweek函数,当前是第几个工作日返回的是从1开始的，
    即一周的开始是周日
    5、验证dayofyear函数,返回一年中第几天
    6、验证hour函数,返回小时
    7、验证 MICROSECOND函数
    8、验证MINUTE函数
    9、验证quarter函数
    10、验证SECOND
    11、验证weekday函数
    12、验证weekofyear函数
    13、验证year函数
    14、清理环境
Expect      :
    1、成功
    2、自动加载成功,无需手动创建
    3、成功
    4、成功
    5、成功
    6、成功
    7、成功
    8、成功(openGauss数据库时间戳中有点表示成了分秒，没有那个点就是小时加分钟)
    9、成功
    10、成功
    11、成功
    12、成功
    13、成功
    14、清理环境完成
History     :modified by z@wan005,2022.08.29，om数据库目前实现自动加载dolphin插件，
             因此删除用例中拷贝插件依赖文件步骤
"""

import os
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node


class PluginTest017(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.common = Common()
        self.sh_primy = CommonSH('PrimaryDbUser')
        self.user_node = Node('PrimaryDbUser')
        self.constant = Constant()
        self.db_name = 'db_plugin0017'

    def test_dolphin(self):
        text = '----step1:创建b库;expect:成功----'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};" \
                  f"create database {self.db_name} DBCOMPATIBILITY ='b';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)

        text = '---step2:b库下查询dolphin插件;expect:自动加载成功,无需手动创建---'
        self.log.info(text)
        sql_cmd = "select extname from pg_extension where extname ='dolphin';"
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual('dolphin', sql_res.splitlines()[-2].strip(),
                         '执行失败' + text)

        text = '-----step3:验证dayofmonth函数,返回日期;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select \
            dayofmonth(timestamp '2021-11-4 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '4',
                         '执行失败:' + text)

        text = '-----step4:验证dayofweek函数,当前是第几个工作日，' \
               '返回的是从1开始的，即一周的开始是周日;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select \
            dayofweek(timestamp '2021-11-4 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '5',
                         '执行失败:' + text)

        text = '-----step5:验证dayofyear函数,返回一年中第几天;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select \
            dayofyear(timestamp '2021-12-31 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '365',
                         '执行失败:' + text)

        text = '-----step6:验证hour函数,返回小时;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select hour(timestamp '2021-11-4 24:00:00');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '0',
                         '执行失败:' + text)

        text = '--step7:验证 MICROSECOND函数;expect:返回微秒,位数为6位,不够补足0--'
        self.log.info(text)
        sql_cmd = f'''select \
            microsecond(timestamp '2021-11-4 16:30:44.341191');
            select microsecond(timestamp '2021-11-4 16:30:44.34119178');
            select microsecond(timestamp '2021-11-4 16:30:44.3411');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        msg = sql_res.splitlines()
        self.log.info(msg)
        self.assertEqual(sql_res.splitlines()[2].strip(), '341191',
                         '执行失败:' + text)
        self.assertEqual(sql_res.splitlines()[7].strip(), '341192',
                         '执行失败:' + text)
        self.assertEqual(sql_res.splitlines()[-2].strip(), '341100',
                         '执行失败:' + text)

        text = '-----step8:验证MINUTE函数;expect:成功,返回分钟---'
        self.log.info(text)
        sql_cmd = f'''select MINUTE(timestamp '2021-11-4 16:30:44.341191');
            select MINUTE(timestamp '2021-11-4 16:59.52');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '30',
                         '执行失败:' + text)
        self.assertEqual(sql_res.splitlines()[-2].strip(), '59',
                         '执行失败:' + text)

        text = '-----step9:验证quarter函数;expect:成功，返回自然季度---'
        self.log.info(text)
        sql_cmd = f'''select quarter(timestamp '2021-11-4 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '4',
                         '执行失败:' + text)

        text = '-----step10:验证SECOND;expect:成功，返回秒---'
        self.log.info(text)
        sql_cmd = f'''select second(timestamp '2021-11-4 16:30:44.341191');
            select second(timestamp '2021-11-4 16:30');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        msg = sql_res.splitlines()
        self.log.info(msg)
        self.assertEqual(sql_res.splitlines()[2].strip(), '44',
                         '执行失败:' + text)
        self.assertEqual(sql_res.splitlines()[-2].strip(), '0',
                         '执行失败:' + text)

        text = '-----step11:验证weekday函数;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select weekday(timestamp '2021-11-4 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '3',
                         '执行失败:' + text)

        text = '-----step12:验证weekofyear函数;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select \
            weekofyear(timestamp '2021-11-4 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '44',
                         '执行失败:' + text)

        text = '-----step13:验证year函数;expect:成功---'
        self.log.info(text)
        sql_cmd = f'''select year(timestamp '2021-11-4 16:30:44.341191');'''
        self.log.info(sql_cmd)
        sql_res = self.sh_primy.execut_db_sql(sql_cmd, dbname=self.db_name)
        self.log.info(sql_res)
        self.assertEqual(sql_res.splitlines()[2].strip(), '2021',
                         '执行失败:' + text)

    def tearDown(self):
        text = '--step14:清理环境;expect:清理环境完成--'
        self.log.info(text)
        sql_cmd = f"drop database if exists {self.db_name};"
        sql_res = self.sh_primy.execut_db_sql(sql_cmd)
        self.log.info(sql_res)
        self.log.info('断言teardown成功')
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, sql_res,
                      '执行失败' + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
